import pandas as pd
from sqlalchemy import create_engine,text   # 创建数据库连接

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/rpa')
# 创建sql语句，使用read_sql_query读取数据
sql_query = 'select * from emp;'
df1 = pd.read_sql_query(sql_query, engine) # 根据sql读取数据
#df1 = pd.read_sql_table('emp', engine) # 根据表名读取数据
print(df1)

# 查询指定数据并修改
df1.loc[df1.emp_id == 1, 'emp_age'] += 1
print(df1)


# 更新特定行的字段
update_query = "UPDATE emp SET emp_age = emp_age + 1 WHERE emp_id = 1;"
# 用临时表更新原表（replace在大数据量时效率高）
with engine.connect() as connection:
    try:
        # 开始事务
        connection.execute(text("START TRANSACTION;"))

        # 删除原表数据
        #connection.execute(text("DELETE FROM emp WHERE emp_id = 1;"))

        # 插入更新后的数据
        connection.execute(text(update_query))

        # 提交事务
        connection.execute(text("COMMIT;"))
    except Exception as e:
        # 回滚事务
        connection.execute(text("ROLLBACK;"))
        print(f"Error occurred: {e}")